﻿using System;
using Microsoft.Office.Interop.Excel;

namespace XD.Office
{
    /// <summary>
    /// Excel模版导出
    /// </summary>
    public partial class XlsTemplateExporter
    {
        #region 定义
        private TemplateParams _param = null;

        private Application _app = null;
        private Workbook _book = null;

        //输出文件
        private string _xlsFile = "";
        //是否已复制模版文件到目标路径
        private bool _hasCopied = false;

        #endregion

        /// <summary>
        ///  Excel模版导出：默认格式
        /// </summary>
        /// <param name="xlsFile">输出文件</param>
        public XlsTemplateExporter(string xlsFile)
        {
            _param = new TemplateParams();
            _xlsFile = xlsFile;
        }

        /// <summary>
        /// Excel模版导出
        /// </summary>
        /// <param name="xlsFile">输出文件</param>
        public XlsTemplateExporter(TemplateParams param, string xlsFile)
        {
            _param = param;
            _xlsFile = xlsFile;
            if (_param == null)
            {
                //自动创建默认配置
                _param = new TemplateParams();
            }
        }

        /// <summary>
        /// 获取、设置 模版参数对象
        /// set：如果已经打开输出流，则不会重新复制、打开模版文件
        /// </summary>
        public TemplateParams Template
        {
            get
            {
                return _param;
            }
            set
            {
                _param = value;
            }
        }

        /// <summary>
        /// 进度控件
        /// </summary>
        public System.Windows.Forms.ProgressBar ProgressBar { get; set; }

        public void Save()
        {
            object missing = Type.Missing;
            if (_hasCopied)
            {
                _book.Save();
            }
            else
            {
                _book.SaveAs(_xlsFile, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                _hasCopied = true;
            }
        }

        public void Close()
        {
            object missing = Type.Missing;
            if (_book != null)
            {
                _book.Close(false, missing, missing);
            }
            if (_app != null)
            {
                _app.Quit();
            }
            _book = null;
            _app = null;
        }

        /// <summary>
        /// 表格和数据卡两种方式混合导出
        /// </summary>
        /// <param name="datarow">数据行</param>
        /// <param name="xlsFile">导出文件</param>
        /// <returns></returns>
        public bool ExportCombo(System.Data.DataRow datarow,System.Data.DataTable table)
        {
            bool ok = false;

            Worksheet sheet = null;
            object missing = Type.Missing;
            try
            {

                string tmpFile = _param.TemplateFile;
                //打卡Excel
                OpenWorkbook();
                sheet = GetWorksheet(_book);

                //获取列名称
                string[] columns = GetColumns(table, sheet);
                //替换方式输出数据
                foreach (string cname in columns)
                {
                    object v = datarow[cname];
                    string value = "";
                    if (v != DBNull.Value)
                    {
                        value = Convert.ToString(v);
                    }
                    //查找字符串
                    string oldStr = string.Format(_param.ReplaceRule, cname.ToUpper());
                    Range range = sheet.Cells.Find(oldStr, missing, missing, missing, missing, XlSearchDirection.xlNext, missing, missing, missing);
                    if (range != null)
                    {
                        sheet.Cells.Replace(oldStr, value, missing, missing, missing, missing, missing, missing);
                    }
                }
                if (ProgressBar != null)
                {
                    ProgressBar.Value = 0;
                    ProgressBar.Maximum = table.Rows.Count;
                }
                //表格方式写入数据
                int startrow = _param.StartRow;
                int startcol = _param.StartColumn;
                for (int row = 0; row < table.Rows.Count && row <= _param.MaxRows; row++)
                {
                    System.Data.DataRow dr = table.Rows[row];
                    for (int col = 0; col < columns.Length; col++)
                    {
                        object v = dr[columns[col]];
                        string value = "";
                        if (v != DBNull.Value)
                        {
                            value = Convert.ToString(v);
                        }
                        sheet.Cells[startrow + row, startcol + col] = value;
                    }
                    if (ProgressBar != null)
                    {
                        ProgressBar.Value = row + 1;
                    }
                }
                ok = true;
            }
            catch (Exception err)
            {
                Console.WriteLine(err.Message);
            }

            return ok;
        }

        /// <summary>
        /// 导出数据卡（单条记录）
        /// </summary>
        /// <param name="datarow">数据行</param>
        /// <param name="xlsFile">导出文件</param>
        /// <returns></returns>
        public bool ExportDataCard(System.Data.DataRow datarow)
        {
            bool ok = false;

            Worksheet sheet = null;
            object missing = Type.Missing;
            try
            {

                string tmpFile = _param.TemplateFile;
                //打卡Excel
                OpenWorkbook();
                sheet = GetWorksheet(_book);

                //获取列名称
                string[] columns = GetColumns(datarow.Table, sheet);
                //替换方式输出数据
                foreach(string cname in columns)
                {
                    object v = datarow[cname];
                    string value = "";
                    if (v != DBNull.Value)
                    {
                        value = Convert.ToString(v);
                    }
                    //查找字符串
                    string oldStr = string.Format(_param.ReplaceRule, cname.ToUpper());
                    object matchCase = (object)false;
                    Range range = sheet.Cells.Find(oldStr, missing, missing, missing, missing, XlSearchDirection.xlNext, matchCase, missing, missing);
                    if (range != null)
                    {
                        if (datarow.Table.Columns[cname].Caption.Equals(Constant.PhotoTag))
                        {
                            //导出图片
                            string fileName = (string)value;                            
                            
                            if (System.IO.File.Exists(fileName))
                            {
                                while (range != null)
                                {
                                    //删除替换文本
                                    range.Value2 = String.Empty;
                                    //添加图片
                                    range.Select();
                                    float picLeft = Convert.ToSingle(range.Left);
                                    float picTop = Convert.ToSingle(range.Top);
                                    System.Drawing.Image image = System.Drawing.Image.FromFile(fileName);
                                    float picWidth = image.Width;
                                    float picHeight = image.Height;
                                    sheet.Shapes.AddPicture(fileName, Microsoft.Office.Core.MsoTriState.msoFalse,
                                            Microsoft.Office.Core.MsoTriState.msoTrue, picLeft,
                                            picTop, picWidth, picHeight);
                                    range = sheet.Cells.Find(oldStr, missing, missing, missing, missing, XlSearchDirection.xlNext, matchCase, missing, missing);
                                }
                            }
                            else
                            {
                                value = String.Empty;
                                sheet.Cells.Replace(oldStr, value, missing, missing, matchCase, missing, missing, missing);
                                range = null;
                            }
                        }
                        else
                        {
                            sheet.Cells.Replace(oldStr, value, missing, missing, matchCase, missing, missing, missing);
                        }
                    }
                }
                ok = true;
            }
            catch (Exception err)
            {
                Console.WriteLine(err.Message);
            }

            return ok;
        }

        /// <summary>
        /// 导出数据表
        /// </summary>
        /// <param name="table"></param>
        /// <param name="xlsFile"></param>
        /// <returns></returns>
        public bool ExportData(System.Data.DataTable table)
        {
            bool ok = false;

            Worksheet sheet = null;
            object missing = Type.Missing;
            try
            {

                string tmpFile = _param.TemplateFile;
                //打卡Excel
                OpenWorkbook();
                sheet = GetWorksheet(_book);

                //获取列名称
                string[] columns = GetColumns(table, sheet);

                if (ProgressBar != null)
                {
                    ProgressBar.Value = 0;
                    ProgressBar.Maximum = Math.Min(table.Rows.Count, _param.MaxRows);
                }
                //写入数据
                int startrow = _param.StartRow;
                int startcol = _param.StartColumn;
                for (int row = 0; row < table.Rows.Count && row < _param.MaxRows; row++)
                {
                    System.Data.DataRow dr = table.Rows[row];
                    for (int col = 0; col < columns.Length; col++)
                    {
                        object v = dr[columns[col]];
                        string value = "";
                        if (v != DBNull.Value)
                        {
                            value = Convert.ToString(v);
                        }
                        if (table.Columns[columns[col]].Caption.Equals(Constant.PhotoTag))
                        {
                            //导出图片
                            string fileName = (string)value;
                            if (System.IO.File.Exists(fileName))
                            {
                                Range range = (Range)sheet.Cells[startrow + row, startcol + col];
                                range.Select();
                                float picLeft = Convert.ToSingle(range.Left);
                                float picTop = Convert.ToSingle(range.Top);
                                System.Drawing.Image image = System.Drawing.Image.FromFile(fileName);
                                float picWidth = image.Width;
                                float picHeight = image.Height;
                                sheet.Shapes.AddPicture(fileName, Microsoft.Office.Core.MsoTriState.msoFalse,
                                     Microsoft.Office.Core.MsoTriState.msoTrue, picLeft,
                                     picTop, picWidth, picHeight);
                            }
                            else
                            {
                                sheet.Cells[startrow + row, startcol + col] = String.Empty;
                            }
                         }
                        else
                        {
                            sheet.Cells[startrow + row, startcol + col] = value;
                        }
                    }
                    if (ProgressBar != null)
                    {
                        ProgressBar.Value = row + 1;
                    }
                }
                ok = true;
            }
            catch (Exception err)
            {
                Console.WriteLine(err.Message);
            }            
            return ok;
        }

        /// <summary>
        /// 获取配置的字段
        /// </summary>
        /// <param name="table"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private string[] GetColumns(System.Data.DataTable table,Worksheet sheet)
        {
            string[] columns = _param.Columns;
            if (columns == null)
            {
                int index = 0;
                columns = new string[table.Columns.Count];
                foreach (System.Data.DataColumn dc in table.Columns)
                {
                    columns[index] = dc.ColumnName;
                    index++;
                }
                //在第一行创建列头
                for (int i = 0; i < columns.Length; i++)
                {
                    sheet.Cells[1, _param.StartColumn + i] = columns[i];
                }
            }
            return columns;
        }

        /// <summary>
        /// 打开Excel文档
        /// </summary>
        /// <param name="app"></param>
        /// <returns></returns>
        private void OpenWorkbook()
        {
            if (_book == null)
            {
                _app = new Application();
                object missing = Type.Missing;
                if (System.IO.File.Exists(_param.TemplateFile))
                {
                    //复制文件
                    System.IO.File.Copy(_param.TemplateFile, _xlsFile, true);
                    _book = _app.Workbooks.Open(_xlsFile, missing, missing, missing, missing, missing, missing
                        , missing, missing, missing, missing, missing, missing, missing, missing);
                    _hasCopied = true;
                }
                else
                {
                    //创建空文档
                    _book = _app.Workbooks.Add(true);
                    _hasCopied = false;
                }
            }
        }

        /// <summary>
        /// 根据配置获取工作表
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        private Worksheet GetWorksheet(Workbook book)
        {
            Worksheet sheet = null;
            object missing = Type.Missing;
            if (book.Worksheets.Count > 0)
            {
                if (_param.TableIndex > book.Worksheets.Count)
                {
                    //超出范围，获取第一个
                    sheet = book.Worksheets[0] as Worksheet;
                }
                else
                {
                    sheet = book.Worksheets[_param.TableIndex] as Worksheet;
                }
            }
            else
            {
                sheet = book.Worksheets.Add(missing, missing, missing, missing) as Worksheet;
            }
            return sheet;
        }
    }
}
